package controller;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import model.CommentModel;
import sql.Db;
import sql.ResultSetHandler;


import model.PostModel;
import model.UserModel;

public class Query {


	public static ArrayList<UserModel> getFriends(String id){
		final ArrayList<UserModel> result = new ArrayList<UserModel>();
		Db db = Db.getDb();
		db.executeQuery(
            "SELECT * FROM friends ,user WHERE fromID = "+id+" AND id = toID AND pending = 0 ORDER BY name ASC",
            new ResultSetHandler() {
                public void handleResult(ResultSet rs) {
                    try {
                        while (rs.next()) {
                            result.add(new UserModel(rs));
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            });
		return result;
	}
	
	public static ArrayList<Post> getNewsFeed(String id) {
		final ArrayList<Post> posts = new ArrayList<Post>();
        final HashMap<String, ArrayList<Comment>> commentMap = new HashMap<String, ArrayList<Comment>>();
        Db db = Db.getDb();

        // get all comments for posts on this news feed and set up a map for postId: comment[]
        db.executeQuery(
            "SELECT comment.*, user.name\n" +
            "FROM comment, post, \n" +
                "(\n" +
                    "SELECT * \n" +
                    "FROM friends, user \n" +
                    "WHERE fromID = " + id + " \n" +
                    "AND id = toID\n" +
                ") F, user\n" +
            "WHERE comment.targetId = post.id \n" +
            "AND post.authorId = F.id\n" +
            "AND comment.authorId = user.id\n" +
            "ORDER BY timestamp ASC",

            new ResultSetHandler() {
                @Override
                public void handleResult(ResultSet rs) {
                    try {
                        while (rs.next()) {
                            CommentModel cm = new CommentModel(rs);
                            if (commentMap.containsKey(cm.targetId)) {
                                commentMap.get(cm.targetId).add(new Comment(cm));
                            } else {
                                ArrayList<Comment> a = new ArrayList<Comment>();
                                a.add(new Comment(new CommentModel(rs)));
                                commentMap.put(cm.targetId, a);
                            }
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        );

        // get all posts
        db.executeQuery(
            "SELECT post.*, F.name\n" +
            "FROM post, \n" +
                "(\n" +
                    "SELECT * \n" +
                    "FROM friends, user \n" +
                    "WHERE fromID = " + id + "\n" +
                    "AND id = toID\n" +
                ") F \n" +
            "WHERE authorID = F.id \n" +
            "ORDER BY timestamp DESC limit 20",
            new ResultSetHandler() {
                @Override
                public void handleResult(ResultSet rs) {
                    try {
                        while (rs.next()) {
                            PostModel pm = new PostModel(rs);
                            // get the comments if they exist
                            if (commentMap.containsKey(pm.postId)) {
                                pm.comments = commentMap.get(pm.postId);
                            }
                            Post p = new Post(pm);
                            posts.add(p);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        );

		return posts;
	}

    public static ArrayList<Post> getPosts(String id) {
	    final ArrayList<Post> posts = new ArrayList<Post>();
		Db db = Db.getDb();

        final HashMap<String, ArrayList<Comment>> commentMap = new HashMap<String, ArrayList<Comment>>();

        // get all comments for all posts with this targetId and set up a map for postId: comment[]
        db.executeQuery("SELECT comment.*, user.name \n" +
            "FROM comment, post, user\n" +
            "WHERE post.targetId = " + id + "\n" +
            "AND comment.targetID = post.id\n" +
            "AND comment.authorID = user.id\n" +
            "ORDER BY comment.timestamp ASC",

            new ResultSetHandler() {
                @Override
                public void handleResult(ResultSet rs) {
                    try {
                        while (rs.next()) {
                            CommentModel cm = new CommentModel(rs);
                            if (commentMap.containsKey(cm.targetId)) {
                                commentMap.get(cm.targetId).add(new Comment(cm));
                            } else {
                                ArrayList<Comment> a = new ArrayList<Comment>();
                                a.add(new Comment(new CommentModel(rs)));
                                commentMap.put(cm.targetId, a);
                            }
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        );

        // get all posts
        db.executeQuery("SELECT *\n" +
            "FROM post, user\n" +
            "WHERE targetId = " + id + "\n" +
            "AND user.id = post.authorId\n" +
            "ORDER BY timestamp DESC",

            new ResultSetHandler() {
                @Override
                public void handleResult(ResultSet rs) {
                    try {
                        while (rs.next()) {
                            PostModel pm = new PostModel(rs);
                            // get the comments if they exist
                            if (commentMap.containsKey(pm.postId)) {
                                pm.comments = commentMap.get(pm.postId);
                            }
                            Post p = new Post(pm);
                            posts.add(p);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        );

		return posts;
	}
}